HOWTOs/Database Handling:

Importing Images (BLOB data)

 by jstaniek, December 2007

Kexi treats images as ordinary database BLOBs.

No matter if you use .kexi files (SQLite) or a server, perform the following steps:

1. Create your table containing one (or more) BLOB field(s) and make sure its design meets your requirements. It is easier to alter it now if you want to change some its properties.

2. Create a number of INSERT INTO statements inserting your images (or any other data) and put them into an .sql file. You may want to do this in a loop using your preferred programming language. Depending on the database backend you're using, there are different ways to encode the binary object in the statement.

a) for .kexi files (SQLite):

See line 666 (yes...) of the example database included into kexi source code:

http://websvn.kde.org/*checkout*/trunk/koffice/kexi/examples/Simple_Database.kexi.sql

 INSERT INTO "kexi__blobs" VALUES(1, X'895.........' [...]

You should encode every byte into an uppercase 2-digit hex numberm, prepend X' characters and append ' character.

b) for MySQL your encoding similar to SQLite's one but starts with 0x and does not contain ' characters

c) PostgreSQL uses octal notation for non-printable bytes -nonascii characters, otherwise it displays characters. More info about this: http://www.postgresql.org/docs/8.2/interactive/datatype-binary.html

You can see Kexi's source code for escaping here:

http://www.kexi-project.org/docs/svn-api/html/kexidb_2utils_8cpp-source.html#l01019 http://www.kexi-project.org/docs/svn-api/html/kexidb_2utils_8cpp-source.html#l01074

3. Once you have file.sql, type this from the command line for SQLite:

 ksqlite mydb.kexi < file.sql

For MySQL or PostgreSQL, use similar command that is described in a handbook for your server.

For MySQL you can see http://www.raditha.com:443/mysql/blob.php if you do not want to create possible large .sql file. Using any db access API (e.g. perl DBI) allows you to import large data in-place instead of using intermediate files.

Closing notes:

• close your Kexi session in you are using .kexi file (SQLite).
• for servers, you should just close the table you want to alter and any (query, form, report..) making use of the table. Reopening the table after performing the external inserting should show you the new contents.

Note that Kexi 1.x does not perform any optimization for blobs loading is you have many records - all of them are loaded in advance into the GUI unless you're using a query that limits given table columns or rows.

Appendix

Andrew Hufton's Perl function to convert a binary file to a hex string, which is needed to create the BLOB data:

 sub Bin2HEX {
  my $bin_file = shift;
  open (BIN, "$bin_file") or die "Could not open $bin_file\n\n";
  binmode BIN;
  my $bin;
  while ( read (BIN, $bin, 1 ) and printf("%02X", ord($bin))){};
  close BIN;
 }